﻿using System;
using System.Collections.Generic;
using System.Text;
using HotelManagement.Common;
using HotelManagement.DL.Helper;
using System.Data;
using System.Data.Common;
using HotelManagement.Common.SQL;

namespace HotelManagement.DL.DAO
{
    public class GoodsDL
    {
        private readonly string SQL_PARA_NAME = "@name";
        private readonly string SQL_PARA_COUNT = "@count";
        private readonly string SQL_PARA_ID = "@id";

        public int Search(ref Goods goods) 
        {
            int nResult = (int)ReturnType.SUCCESS;
            try
            {
                DBHelper dbHelper = DBHelperFactory.createDBHelper();
                DbCommand command = null;
                if (string.IsNullOrEmpty(goods.Name))
                {
                    command = dbHelper.CreateCommandBySql(SqlFile.SQL_SEARCH_GOODS);
                }
                else 
                {
                    command = dbHelper.CreateCommandBySql(SqlFile.SQL_SEARCH_GOODS_BYNAME);
                    dbHelper.AddInParameter(command, SQL_PARA_NAME, DbType.String, goods.Name);
                }
                
                DataSet ds = dbHelper.ExecuteDataSetQuery(command);
                if (0 == ds.Tables.Count)
                {
                    nResult = (int)ReturnType.FAILED;
                }
                goods.GoodsCollection = ds.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }

            return nResult;
        }

        public int SearchWarnGoods(ref Goods goods)
        {
            int nResult = (int)ReturnType.SUCCESS;
            try
            {
                DBHelper dbHelper = DBHelperFactory.createDBHelper();
                DbCommand command = null;
                if (string.IsNullOrEmpty(goods.Name))
                {
                    command = dbHelper.CreateCommandBySql(SqlFile.SQL_SEARCH_WARN_GOODS);
                }
                else
                {
                    command = dbHelper.CreateCommandBySql(SqlFile.SQL_SEARCH_GOODS_BYNAME);
                }

                DataSet ds = dbHelper.ExecuteDataSetQuery(command);
                if (0 == ds.Tables.Count)
                {
                    nResult = (int)ReturnType.FAILED;
                }
                goods.GoodsCollection = ds.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }

            return nResult;
        }

        public int GetGoodsName(ref DataTable nameList) 
        {
            int nResult = (int)ReturnType.SUCCESS;
            try
            {
                DBHelper dbHelper = DBHelperFactory.createDBHelper();
                DbCommand command = dbHelper.CreateCommandBySql(SqlFile.SQL_SEARCH_GOODS_NAME);
 
                DataSet ds = dbHelper.ExecuteDataSetQuery(command);
                if (0 == ds.Tables.Count)
                {
                    nResult = (int)ReturnType.FAILED;
                }
                nameList = ds.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }

            return nResult;
        }

        public int PurchaseGoods(Goods goodsInfo, Trade tradeInfo) 
        {
            int nResult = (int)ReturnType.SUCCESS;
            DBHelper dbHelper = DBHelperFactory.createDBHelper();
            DbTransaction trans = dbHelper.GetTransaction();
            try
            {
                DbCommand command = dbHelper.CreateCommandBySql(SqlFile.SQL_PURCHASE_GOODS);
                command.Parameters.Clear();
                dbHelper.AddInParameter(command, "count", DbType.Int32, goodsInfo.Count);
                dbHelper.AddInParameter(command, SQL_PARA_ID, DbType.String, goodsInfo.ID);
                nResult = dbHelper.ExecuteNonQuery(command, trans);
                TradeDL tradeDL = new TradeDL();
                nResult = tradeDL.AddTrade(tradeInfo, trans);
                trans.Commit();
            }
            catch (Exception)
            {
                trans.Rollback();
                throw;
            }

            return nResult;
        }

        public int SaleGoods(Goods goodsInfo, Trade tradeInfo)
        {

            int nResult = (int)ReturnType.SUCCESS;
            DBHelper dbHelper = DBHelperFactory.createDBHelper();
            DbTransaction trans = dbHelper.GetTransaction();
            try
            {
                DbCommand command = dbHelper.CreateCommandBySql(SqlFile.SQL_SALE_GOODS);
                dbHelper.AddInParameter(command, "Count", DbType.Int32, goodsInfo.Count);
                dbHelper.AddInParameter(command, SQL_PARA_ID, DbType.String, goodsInfo.ID);
                command.Transaction = trans;
                nResult = dbHelper.ExecuteNonQuery(command, trans);
                TradeDL tradeDL = new TradeDL();
                nResult = tradeDL.AddTrade(tradeInfo, trans);
                trans.Commit();
            }
            catch (Exception)
            {
                trans.Rollback();
                throw;
            }

            return nResult;
        }

        public int UpdateGoods(Goods goods) 
        {
            int nResult = (int)ReturnType.SUCCESS;
            try
            {
                DBHelper dbHelper = DBHelperFactory.createDBHelper();
                DbCommand command = dbHelper.CreateCommandBySql(SqlFile.SQL_UPDATE_GOODS);
                dbHelper.AddInParameter(command, "pruchase", DbType.String, goods.Name);
                dbHelper.AddInParameter(command, "pruchase", DbType.Single, goods.PurchasePrice);
                dbHelper.AddInParameter(command, "sale", DbType.Single, goods.SalePrice);
                dbHelper.AddInParameter(command, "warn", DbType.Int32, goods.WaringCount);

                dbHelper.AddInParameter(command, SQL_PARA_ID, DbType.Int32, goods.ID);

                nResult = dbHelper.ExecuteNonQuery(command);
            }
            catch (Exception)
            {
                throw;
            }

            return nResult;
        }

        public int AddGoods(Goods goods)
        {
            int nResult = (int)ReturnType.SUCCESS;
            try
            {
                DBHelper dbHelper = DBHelperFactory.createDBHelper();
                DbCommand command = dbHelper.CreateCommandBySql(SqlFile.SQL_ADD_GOODS);
                dbHelper.AddInParameter(command, "Gname", DbType.String, goods.Name);
                dbHelper.AddInParameter(command, "PurchasePrice", DbType.Single, goods.PurchasePrice);
                dbHelper.AddInParameter(command, "SalePrice", DbType.Single, goods.SalePrice);
                dbHelper.AddInParameter(command, "WaringCount", DbType.Int32, goods.WaringCount);


                nResult = dbHelper.ExecuteNonQuery(command);
            }
            catch (Exception)
            {
                throw;
            }

            return nResult;
        }

        public int DeleteGoods(Goods goods)
        {
            int nResult = (int)ReturnType.SUCCESS;
            try
            {
                DBHelper dbHelper = DBHelperFactory.createDBHelper();
                DbCommand command = dbHelper.CreateCommandBySql(SqlFile.SQL_DELETE_GOODS);
                dbHelper.AddInParameter(command, SQL_PARA_ID, DbType.String, goods.ID);

                nResult = dbHelper.ExecuteNonQuery(command);
            }
            catch (Exception)
            {
                throw;
            }

            return nResult;
        }
    }
}
